﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace AcctrueWMS.Foundation.DAL
{
    class SqlOpeartor : IDBOperator
    {
        private const string strQueryTablesSQL = " select name   as tableName  from sysobjects where type='U'  ";     //获取系统中的用户表
        private const string strQueryCloumnsSQL = " SELECT  NAME,length,'' as DataType FROM dbo.syscolumns   ";     //获表的列信息
        private static readonly String connString = DBFactory.ConnString;

        #region 获取数据库时间
        public DateTime GetDBDateTime()
        {
            DateTime rtnDateTime = DateTime.Now;
            string GetDateTimeSQL = " select  getdate() FROM sysobjects  ";
            try
            {
                rtnDateTime = Convert.ToDateTime(SqlHelper.ExecuteScalar(connString, System.Data.CommandType.Text, GetDateTimeSQL));
            }
            catch (Exception)
            { }
            return rtnDateTime;
        }
        #endregion

        #region 获取数据库中所有用户表
        public DataTable GetAllTables(string condition)
        {
            string queryTableSQL = strQueryTablesSQL;
            if (condition != null && condition.Trim() != "")
            {
                queryTableSQL += string.Format(" and  ( name like '%{0}%')  ", condition.Trim());
            }
            return SqlHelper.ExecuteDataset(connString, System.Data.CommandType.Text, queryTableSQL).Tables[0];
        }
        #endregion

        #region 获取表的列信息
        public DataTable GetColumnsInfo(string tableName)
        {
            DataTable tableColumnsInfo = new DataTable();
            string queryColumnsSQL = strQueryCloumnsSQL;
            if (tableName != null && tableName.Trim() != "")
            {
                queryColumnsSQL += string.Format("  where id=object_id('{0}')  ", tableName);
            }
            else
            {
                queryColumnsSQL += "  where 1=2  ";
            }
            using (tableColumnsInfo = SqlHelper.ExecuteDataset(connString, System.Data.CommandType.Text, queryColumnsSQL).Tables[0])
                return tableColumnsInfo;
        }
        #endregion

        #region 保存DataTables
        //保存成功后返回主键
        /// <summary>
        ///  功能：保存页面数据，并返回主键
        /// </summary>
        /// <param name="tableModels">table 集合</param>
        /// <param name="mode">数据模型名称</param>
        /// <returns>保存成功后返回主键值</returns>
        public string SaveTables(TableModel[] tableModels, string mode)
        {
            string rtnKeyValues = string.Empty;
            if (tableModels.Length > 0)
            {
                DataTable mainTable = tableModels[0].DataTable;
                string mainTableName = mainTable.TableName;
                string[] keyNames = tableModels[0].KeyName.Split(',');    //可能出现联合主键
                rtnKeyValues = GetKeyValues(mainTable, keyNames);           //获取联合主键值          
                string commitSQL = string.Empty;
                string saveDetailSQL = string.Empty;
                using (SqlConnection conn = new SqlConnection(connString))
                {
                    //用事务提交保存      
                    if (conn.State != ConnectionState.Open) conn.Open();
                    IDbTransaction tran = conn.BeginTransaction();
                    if (mode == "1")    //新增
                    {
                        commitSQL = GetInsertSQL(mainTable, "", "");                          //保存主表记录                               
                        if (!string.IsNullOrEmpty(rtnKeyValues))
                        {
                            for (int i = 1; i < tableModels.Length; i++)              //如果有多个从表，则依次保存
                            {
                                if (tableModels[i] == null) continue;
                                string ForeignkeyName = tableModels[i].ForeignName.Trim();
                                saveDetailSQL += GetInsertSQL(tableModels[i].DataTable, ForeignkeyName, rtnKeyValues);
                            }
                            if (!string.IsNullOrEmpty(saveDetailSQL))
                            {
                                commitSQL += saveDetailSQL;
                            }
                            WMSqlExcuter.ExecuteNonQuery(tran, commitSQL, CommandType.Text);
                        }
                    }
                    else  //修改
                    {
                        commitSQL = GetUpdateSQL(mainTable, keyNames, null, "");
                        if (!string.IsNullOrEmpty(rtnKeyValues))
                        {
                            string ForignKeyValues = GetKeyValues(tableModels[0].DataTable, keyNames);
                            for (int i = 1; i < tableModels.Length; i++)    //如果有多个从表，则依次保存
                            {
                                string ForeignkeyName = tableModels[i].ForeignName.Trim();
                                saveDetailSQL += GetUpdateSQL(tableModels[i].DataTable, null, ForeignkeyName, ForignKeyValues);
                            }
                            if (!string.IsNullOrEmpty(saveDetailSQL))
                            {
                                commitSQL += saveDetailSQL;
                            }
                            WMSqlExcuter.ExecuteNonQuery(tran, commitSQL, CommandType.Text);
                        }
                    }
                    tran.Commit();
                    if (conn.State != ConnectionState.Closed) conn.Close();
                }
            }
            return rtnKeyValues;
        }
        
  
        /// <summary>
        /// 功能：生成新增的sql语句
        /// </summary>
        /// <param name="dt">数据表</param>
        /// <param name="foreignkeyName">外键名</param>
        /// <param name="foreignKeyValue">外键值</param>
        /// <returns>返回拼接生成 的新增sql语句</returns>
        private string GetInsertSQL(DataTable dt, string foreignkeyName, string foreignKeyValue)
        {
            string saveSQL = string.Empty;
            string mainTableName = dt.TableName;
            object Value;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                saveSQL += string.Format("  insert into  {0} ( ", mainTableName);
                for (int j = 0; j < dt.Columns.Count; j++)   //遍历所有列名称
                {
                    saveSQL += dt.Columns[j].ColumnName.ToString() + "  ,";
                }
                saveSQL = string.Format("{0} ) values (", saveSQL.Substring(0, saveSQL.Length - 1));
                for (int k = 0; k < dt.Columns.Count; k++)  //获取所有列的值
                {
                    Value = dt.Rows[i][k];
                    //如果列为外键，则处理外键取值
                    if (!string.IsNullOrEmpty(foreignkeyName) && !string.IsNullOrEmpty(foreignKeyValue))  
                    {
                        Value = GetForeignKeyValue(Value, dt.Columns[k].ColumnName, foreignkeyName, foreignKeyValue);
                    }
                    if (Value==DBNull.Value) saveSQL += "NULL,";
                    else
                    {
                        saveSQL += " '" + Value.ToString().Trim() + "' ,";
                    }
                }
                saveSQL = saveSQL.Substring(0, saveSQL.Length - 1) + " )  ;";
            }
            return saveSQL;
        }

        //修改的sql语句
        /// <summary>
        ///功能： 生成修改的sql语句
        /// </summary>
        /// <param name="dt">数据表</param>
        /// <param name="keyName">主键名</param>
        /// <param name="foreignkeyNames">外键名</param>
        /// <param name="foreignKeyValues">外键值</param>
        /// <returns>返回生成 的修改的sql语句</returns>
        private string GetUpdateSQL(DataTable dt, string[] keyName, string foreignkeyNames, string foreignKeyValues)
        {
            string SaveSQL = string.Empty;
            string TableName = dt.TableName;
            if (foreignkeyNames == null)  //更新主表信息
            {
                if (keyName.Length >= dt.Columns.Count) return SaveSQL; //没有需要更新的列
                SaveSQL = string.Format("update  {0}  set   ", TableName);
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    if (!CheckContainsItem(dt.Columns[j].ColumnName, keyName))  //排除主键值
                    {
                        if (dt.Rows[0][j]==DBNull.Value )
                        {
                            SaveSQL += string.Format("  {0}=NULL,", dt.Columns[j].ColumnName.ToString());
                        }
                        else
                        {
                            SaveSQL += string.Format("  {0}='{1}',", dt.Columns[j].ColumnName.ToString(), dt.Rows[0][j].ToString().Trim());
                        }        
                    }
                }
                SaveSQL.Remove(SaveSQL.Length - 1, 1);
                SaveSQL = string.Format("{0} where 1=1   {1}  ; ", SaveSQL.Substring(0, SaveSQL.Length - 1), MakeKeyName_Value(dt, keyName));
            }
            else    //更新明细表：先删除，在插入
            {
                SaveSQL = string.Format("delete from  {0}  where   1=1   {1} ;", TableName, CreateKeyValuesSQL(foreignkeyNames, foreignKeyValues));
                SaveSQL += GetInsertSQL(dt, foreignkeyNames, foreignKeyValues);
            }
            return SaveSQL;
        }

        /// <summary>
        ///功能： 获取table 的主键值
        /// </summary>
        /// <param name="dt">数据表</param>
        /// <param name="keyNames">主键名</param>
        /// <returns>主键值</returns>
        private string GetKeyValues(DataTable dt, string[] keyNames)
        {
            string KeyValues = string.Empty;
            foreach (string keyName in keyNames)                            //  获取主表主键值 做从表外键
            {
                KeyValues += dt.Rows[0][keyName].ToString() + ",";
            }
            if (!string.IsNullOrEmpty(KeyValues))
            {
                KeyValues = KeyValues.Substring(0, KeyValues.Length - 1);
            }
            return KeyValues;
        }

        /// <summary>
        /// 功能：如果列名属于外键，则返回外键值
        /// </summary>
        /// <param name="value">值</param>
        /// <param name="ColumnName">列名</param>
        /// <param name="foreignkeyName">外键名</param>
        /// <param name="foreignKeyValue">外键值</param>
        /// <returns></returns>
        private Object GetForeignKeyValue(object value, string ColumnName, string foreignkeyName, string foreignKeyValue)
        {           
            string[] arrayForeignkeyName = foreignkeyName.Split(',');
            string[] arrayForeignKeyValue = foreignKeyValue.Split(',');
            if (arrayForeignkeyName.Length != arrayForeignKeyValue.Length) return value;  //如果外键列数不相等，则直接返回
            for (int m = 0; m < arrayForeignkeyName.Length; m++)
            {
                if (ColumnName.ToUpper() == arrayForeignkeyName[m].ToUpper())       //如果是外键，则值为 ForeignKeyValue
                {
                    value = arrayForeignKeyValue[m];
                    break;
                }
            }
            return value;
        }
        //判断数组中是否包含指定字符串
        private bool CheckContainsItem(string express, string[] array)
        {
            if (array.Length <= 0) return false;
            foreach (string item in array)
            {
                if (item.ToLower().Contains(express.ToLower()))
                {
                    return true;
                }
            }
            return false;
        }
        //把表的主键名和值相匹配
        private string MakeKeyName_Value(DataTable dt, string[] keyNames)
        {
            string express = string.Empty;
            if (keyNames.Length < 0) return "and (1=2)";
            foreach (string KeyName in keyNames)
            {
                if (dt.Columns.Contains(KeyName))
                {
                    express += string.Format("  and ( {0}='{1}' ) ", KeyName, dt.Rows[0][KeyName]);
                }
            }
            return express;
        }
        //生成主键名和值的表达式
        private string CreateKeyValuesSQL(string names, string values)
        {
            string express = string.Empty;
            string[] arrayNames = names.Split(',');
            string[] arrayValues = values.Split(',');
            if (arrayNames.Length != arrayValues.Length) return " and  (1=2) ";  //列名和值数目不匹配
            for (int i = 0; i < arrayNames.Length; i++)
            {
                express += string.Format("  and ( {0}='{1}' ) ", arrayNames[i], arrayValues[i]);
            }
            return express;
        }

        #endregion


        /// <summary>
        /// 获取订单编码
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="colTag">列名</param>
        /// <returns>生成自动编号</returns>
        public int GetSequenceNo(string tableName, string colTag)
        {
            int iRtn = 0;
            if (colTag == "") colTag = "main"; //给默认值       
            using (SqlConnection conn = new SqlConnection(connString))
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                IDbTransaction tran = conn.BeginTransaction();
                DateTime nowData = GetDBDateTime();
                string getSeqNoSQL = string.Format("select   seqNo from sys_sequenceNo where tableName='{0}' and  createDate='{1}' and colTag='{2}'  ", tableName.Trim(), nowData.ToShortDateString().ToString(), colTag.Trim());
                object value = WMSqlExcuter.ExecuteScalar(tran, getSeqNoSQL, CommandType.Text);
                string seqNo = string.Empty;
                if (value != null)
                {
                    seqNo = value.ToString();
                }
                string updateSQL = string.Empty;
                if (!string.IsNullOrEmpty(seqNo))
                {
                    iRtn = int.Parse(seqNo) + 1;
                    updateSQL = string.Format(" update  sys_sequenceNo set  seqNo={0}  where  tableName='{1}' and colTag='{2}'  and   createDate='{3}' ", iRtn, tableName.Trim(), colTag.Trim(), nowData.ToShortDateString().ToString());
                }
                else
                {
                    iRtn = 1;
                    updateSQL = string.Format(" insert into   sys_sequenceNo (tableName,colTag,createDate,seqNo) values ('{0}','{1}','{2}' ,{3}) ", tableName.Trim(), colTag.Trim(), nowData.ToShortDateString().ToString(), iRtn);
                }
                WMSqlExcuter.ExecuteNonQuery(updateSQL);
                tran.Commit();
            }
            return iRtn;
        }


        /// <summary>
        /// 执行事务
        /// </summary>
        /// <param name="cmdText">所需要执行的sql</param>
        /// <returns>返回执行所影响的行</returns>
        public int ExecuteTran(string cmdText)
        {
            using (SqlConnection conn = new SqlConnection(connString))
            {
                if (conn.State != ConnectionState.Open)
                {
                    conn.Open();
                }
                IDbTransaction tran = conn.BeginTransaction();
                WMSqlExcuter.ExecuteNonQuery(tran, cmdText, CommandType.Text);
                return 1;
            }
        }


    }
}